﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Adams.Admin.DataAccess.Sitemap
{
    public static class Index
    {
        public static Adams.Admin.DataTransfer.Sitemap.Index.PageLoad PageLoad()
        {
            var data = new Adams.Admin.DataTransfer.Sitemap.Index.PageLoad();
            var nodes = new Shared.Entities.Sitemap.NodeCollection();

            using (SqlDataReader dr = DB.RunDataReader(@"
WITH AllNodes AS (
	SELECT n.NodeID, n.ParentNodeID, n.DisplayOrder, 0 AS [Level], n.Title, n.Url
	FROM Sitemap.Nodes n
	WHERE NodeID = 1
	UNION ALL
	SELECT n.NodeID, n.ParentNodeID, n.DisplayOrder, a.[Level] + 1, n.Title, n.Url
	FROM Sitemap.Nodes n JOIN AllNodes a ON n.ParentNodeID = a.NodeID 
)
SELECT * 
FROM AllNodes
ORDER BY Level, DisplayOrder
", CommandType.Text))
            {
                while (dr.Read())
                {
                    var node = new Shared.Entities.Sitemap.Node();
                    node.ID = dr["NodeID"].DbInt();
                    node.Url = dr["Url"].DbString();
                    node.Title = dr["Title"].DbString();
                    node.DisplayOrder = dr["DisplayOrder"].DbInt();
                    node.ParentNode = new Shared.Entities.Sitemap.Node() {
                        ID = dr["ParentNodeID"].DbInt()
                    };
                    node.ChildNodes = new Shared.Entities.Sitemap.NodeCollection();
                    nodes.Add(node);
                }
            }

            foreach (var node in nodes)
            {
                if (node.ID == 1)
                {
                    data.HomeNode = node;
                }
                else
                {
                    nodes.Where(n => n.ID == node.ParentNode.ID).FirstOrDefault().ChildNodes.Add(node);
                }
            }

            return data;
        }

        public static void Update(Adams.Admin.DataTransfer.Sitemap.Index.UpdateInput input)
        {
            StringBuilder sb = new StringBuilder();
            foreach (var node in input.Nodes)
            {
                sb.Append(@"
UPDATE Sitemap.Nodes SET ParentNodeID = @ParentNodeID, DisplayOrder = " + node.DisplayOrder + @" WHERE NodeID = " + node.ID + @"
");
            }

            DB.Run(sb.ToString(), CommandType.Text,
                DB.CreateParameter("@ParentNodeID", input.ParentNodeID));
        }

        public static Adams.Admin.DataTransfer.Sitemap.Index.GetNode GetNode(Adams.Admin.DataTransfer.Sitemap.Index.GetNodeInput input)
        {
            var data = new Adams.Admin.DataTransfer.Sitemap.Index.GetNode();
            
            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT * 
FROM Sitemap.Nodes 
WHERE NodeID = @NodeID
", CommandType.Text,
    DB.CreateParameter("@NodeID", input.NodeID)))
            {
                while (dr.Read())
                {
                    var node = new Shared.Entities.Sitemap.Node();
                    node.ID = dr["NodeID"].DbInt();
                    node.Url = dr["Url"].DbString();
                    node.Title = dr["Title"].DbString();
                    node.DisplayOrder = dr["DisplayOrder"].DbInt();
                    data.Node = node;
                }
            }

            return data;
        }

        public static int SaveNode(DataTransfer.Sitemap.Index.NodeFormInput input)
        {
            return DB.RunScalar(@"
IF(@NodeID = 0)
BEGIN
    INSERT Sitemap.Nodes (ParentNodeID, Title, Url, DisplayOrder)
    VALUES (@ParentNodeID, @Title, @Url, @DisplayOrder)
    SELECT SCOPE_IDENTITY() AS NodeID
END
ELSE
BEGIN
    UPDATE Sitemap.Nodes
    SET Title = @Title, Url = @Url
    WHERE NodeID = @NodeID
    SELECT @NodeID AS NodeID
END
", CommandType.Text,
                DB.CreateParameter("@NodeID", input.ID),
                DB.CreateParameter("@ParentNodeID", input.ParentNodeID.ToNString()),
                DB.CreateParameter("@DisplayOrder", input.DisplayOrder.ToNString()),
                DB.CreateParameter("@Title", input.Title),
                DB.CreateParameter("@Url", input.Url)).DbInt();
        }

        public static Adams.Admin.DataTransfer.Sitemap.Index.NodeInfo GetNodeInfo(string url, int nodeId)
        {
            var data = new Adams.Admin.DataTransfer.Sitemap.Index.NodeInfo();

            using (SqlDataReader dr = DB.RunDataReader(@"
IF (@NodeID > 0)
BEGIN
    SELECT Url
    FROM Sitemap.Nodes
    WHERE NodeID = @NodeID
END

SELECT COUNT(*) AS Count
FROM Sitemap.Nodes
WHERE Url = @Url
", CommandType.Text,
                DB.CreateParameter("@NodeID", nodeId),
                DB.CreateParameter("@Url", url)))
            {
                if (nodeId > 0)
                {
                    dr.Read();
                    data.Url = dr["Url"].DbString();
                    dr.NextResult();
                }
                dr.Read();
                data.Exists = dr["Count"].DbInt() > 0;
            }

            return data;
        }

        public static void DeleteNode(int nodeId)
        {
            DB.Run(@"
DELETE Sitemap.Nodes WHERE NodeID = @NodeID
", CommandType.Text,
    DB.CreateParameter("@NodeID", nodeId));
        }
    }
}
